﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PoJun.Tools.File
{
    /// <summary>
    /// Excel操作
    /// </summary>
    public class Excel
    {
        #region 导出Excel

        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="dataTable">DataTable</param>
        /// <param name="path">输出路径</param>
        /// <param name="fileName">文件名称</param>
        /// <param name="tableName">表名</param>
        public static void ExportExcel(DataTable dataTable, string path, string fileName, string tableName = "Sheet1")
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(tableName);

            ICellStyle HeadercellStyle = workbook.CreateCellStyle();
            HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            //字体
            NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
            headerfont.Boldweight = (short)FontBoldWeight.Bold;
            HeadercellStyle.SetFont(headerfont);

            //用column name 作为列名
            int icolIndex = 0;
            IRow headerRow = sheet.CreateRow(0);
            foreach (DataColumn item in dataTable.Columns)
            {
                ICell cell = headerRow.CreateCell(icolIndex);
                cell.SetCellValue(item.ColumnName);
                cell.CellStyle = HeadercellStyle;
                icolIndex++;
            }

            ICellStyle cellStyle = workbook.CreateCellStyle();

            //为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
            cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
            cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

            NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
            cellfont.Boldweight = (short)FontBoldWeight.Normal;
            cellStyle.SetFont(cellfont);

            //建立内容行
            int iRowIndex = 1;
            int iCellIndex = 0;
            foreach (DataRow Rowitem in dataTable.Rows)
            {
                IRow DataRow = sheet.CreateRow(iRowIndex);
                foreach (DataColumn Colitem in dataTable.Columns)
                {

                    ICell cell = DataRow.CreateCell(iCellIndex);
                    cell.SetCellValue(Rowitem[Colitem].ToString());
                    cell.CellStyle = cellStyle;
                    iCellIndex++;
                }
                iCellIndex = 0;
                iRowIndex++;
            }

            //TODO:因为性能损耗太大所以暂时注释掉
            ////自适应列宽度
            //for (int i = 0; i < icolIndex; i++)
            //{
            //    sheet.AutoSizeColumn(i);
            //}

            path = System.Web.HttpContext.Current.Server.MapPath(path);

            //判断文件夹是否存在，如果不存在就创建文件夹
            if (!Directory.Exists(path))
            {
                //创建文件夹
                Directory.CreateDirectory(path);
            }

            //写Excel
            FileStream file = new FileStream(path + fileName, FileMode.OpenOrCreate);
            workbook.Write(file);
            file.Flush();
            file.Close();
        }

        #endregion

        #region 将excel中的数据导入到DataTable中

        /// <summary>
        /// 将excel中的数据导入到DataTable中
        /// </summary>
        /// <param name="fileName">文件地址</param>
        /// <param name="sheetName">excel工作薄sheet的名称</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
        /// <param name="allowDuplicateColumns">允许重复的列（如果遇到重复的列默认在列名后面加1，示：Name1）</param>
        /// <returns>返回的DataTable</returns>
        public static DataTable ExcelToDataTable(string fileName, string sheetName = null, bool isFirstRowColumn = false,bool allowDuplicateColumns = false)
        {
            IWorkbook workbook = null;
            FileStream fs = null;
            ISheet sheet = null;
            DataTable data = new DataTable();
            int startRow = 0;
      
			fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
			if (fileName.IndexOf(".xlsx") > 0) // 2007版本
				workbook = new XSSFWorkbook(fs);
			else if (fileName.IndexOf(".xls") > 0) // 2003版本
				workbook = new HSSFWorkbook(fs);

			if (sheetName != null)
			{
				sheet = workbook.GetSheet(sheetName);
				if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
				{
					sheet = workbook.GetSheetAt(0);
				}
			}
			else
			{
				sheet = workbook.GetSheetAt(0);
			}
			if (sheet != null)
			{
				IRow firstRow = sheet.GetRow(0);
				int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数

				if (isFirstRowColumn)
				{
					for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
					{
						ICell cell = firstRow.GetCell(i);
						if (cell != null)
						{
							string cellValue = cell.StringCellValue;
							if (cellValue != null)
							{
                                if (allowDuplicateColumns && data.Columns.Contains(cellValue))
                                {
                                    cellValue = cellValue + 1;
                                }
								DataColumn column = new DataColumn(cellValue);
								data.Columns.Add(column);
							}
						}
					}
					startRow = sheet.FirstRowNum + 1;
				}
				else
				{
					startRow = sheet.FirstRowNum;
				}

				//最后一列的标号
				int rowCount = sheet.LastRowNum;
				if (!isFirstRowColumn && rowCount > 0)
				{
					DataColumn column = new DataColumn();
					data.Columns.Add(column);
				}
				for (int i = startRow; i <= rowCount; ++i)
				{
					IRow row = sheet.GetRow(i);
					if (row == null) continue; //没有数据的行默认是null　　　

					DataRow dataRow = data.NewRow();
					for (int j = row.FirstCellNum; j < cellCount; ++j)
					{
						if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
						{
							dataRow[j] = row.GetCell(j).ToString();
							//dataRow.SetField(0, row.GetCell(j).ToString());
						}
					}
					data.Rows.Add(dataRow);
				}
			}

			return data;           
        }

        #endregion 
    }
}
